package com.hutao.manage.util;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


 

public class ExcelDataUtil{
	
	
	

 

	/**
	 * 读取后缀为xlsx的excel文件的数据
	 * 
	 * @param path
	 * @return List<StudentBean>
	 * @author zhang 2015-08-18 00:08
	 */
	public static List<?> readXlsx(String path) {
        List<Map<String,Object>> list = new LinkedList<>();
        Map<String,Object> map= null;
		XSSFWorkbook xssfWorkbook = null;
		InputStream is = null;
		try {
			 is = new FileInputStream(path);
			xssfWorkbook = new XSSFWorkbook(is);
		} catch (IOException e) {
			e.printStackTrace();
		}finally {
			if(is!=null)
				try {
					is.close();
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		
		if (xssfWorkbook != null) {
			// Read the Sheet
			for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
				XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
				if (xssfSheet == null) {
					continue;
				}
				// Read the Row
				
				for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
					XSSFRow xssfRow = xssfSheet.getRow(rowNum);
					int lastColumn = xssfRow.getLastCellNum();
					map = new LinkedHashMap<>();
					for (int i = 0; i < lastColumn; i++) {
						Cell cell = xssfRow.getCell(i);
						map.put(i+"", getCellValue(cell));
					}
					list.add(map);
				}

/*				for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
					XSSFRow xssfRow = xssfSheet.getRow(rowNum);
					
					if (xssfRow != null) {
						
						int coloumNum=xssfSheet.getRow(rowNum).getPhysicalNumberOfCells();
						
						//System.out.println(xssfRow.getLastCellNum());
						map = new LinkedHashMap<>();
					    for (int i = 0; i < coloumNum; i++) {
							map.put(i+"", getValue(xssfRow.getCell(i)));
						}
					   
					    list.add(map);

					}
				}*/
			}
		}
		return list;
	}
	
	

/**
 * 对Excel的各个单元格的格式进行判断并转换
 */
private static String getCellValue(Cell cell) { 
        String cellValue = ""; 
        if(cell == null)return null;
        DecimalFormat df = new DecimalFormat("#"); 
        DecimalFormat df1 = new DecimalFormat("#.##"); 
        switch (cell.getCellType()) { 
        case HSSFCell.CELL_TYPE_STRING: 
            cellValue =cell.getRichStringCellValue().getString().trim(); 
            break; 
        case HSSFCell.CELL_TYPE_NUMERIC: 
        	double num = cell.getNumericCellValue();
        	if(containsDecimalPoint(num)) {
        		cellValue =df.format(cell.getNumericCellValue()).toString(); 
        	}else {
        		cellValue =df1.format(cell.getNumericCellValue()).toString(); 
        	}
            
            break; 
        case HSSFCell.CELL_TYPE_BOOLEAN: 
            cellValue =String.valueOf(cell.getBooleanCellValue()).trim(); 
            break; 
        case HSSFCell.CELL_TYPE_FORMULA: 
            cellValue =cell.getCellFormula(); 
            break; 
        default: 
            cellValue = ""; 
        } 
        return cellValue; 

  }


  public static boolean  containsDecimalPoint(double num) {
      double eps = 1e-10;
	  boolean isInteger = num - Math.floor(num) < eps;
	  return isInteger;
  }

	
	
	public static void main(String[] args) throws ParseException {
		Map<String,Object> codeMap = new HashMap<>();
		List<Map<String,Object>> codeList= (List<Map<String, Object>>) readXlsx("D://1.xlsx");
		for (Map<String, Object> oldCodeMap : codeList) {
		    String oldCode = (String)oldCodeMap.get("0");
		    String newCode = (String)oldCodeMap.get("1");
			codeMap.put(oldCode, newCode);
		}
		
		List<Map<String,Object>> quanxianList= (List<Map<String, Object>>) readXlsx("D://4.xlsx");
		StringBuilder builder = null;
		
		
		for (Map<String, Object> quanxian : quanxianList) {
			
			String oldCodeStr = (String)quanxian.get("4");
			builder = new StringBuilder();
			String[] oldCodeArr = oldCodeStr.split(",");
			for (int i=1;i<=oldCodeArr.length;i++) {
				if(i==oldCodeArr.length) {
					builder.append((String)codeMap.get(oldCodeArr[i-1]));
				}else {
					builder.append((String)codeMap.get(oldCodeArr[i-1])).append(",");
				}
				
				
			}
			
			quanxian.put("4", builder.toString());
			
			
			
			System.out.println(quanxian);
		}	
		
		
	
	        
	}
	
	
	 public static String dateToStamp(String s) throws ParseException{
	       
	        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm");
	        long time = new Date("1970-01-01").getTime();
	        double d = 43598.68194444444*24*60*60;
	        long ts = Math.round(d);
	        Date date = new Date(ts+time);
	        return simpleDateFormat.format(date);
	    }


	/**
	 * 判断后缀为xlsx的excel文件的数据类型
	 * 
	 * @param xssfRow
	 * @return String
	 * @author zhang 2015-08-18 00:12
	 */
	@SuppressWarnings("static-access")
	private static String getValue(XSSFCell xssfRow) {
		if(xssfRow!=null) {
		if (xssfRow.getCellType() == xssfRow.CELL_TYPE_BOOLEAN) {
			return String.valueOf(xssfRow.getBooleanCellValue());
		} else if (xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC) {
			return String.valueOf(xssfRow.getNumericCellValue());
		} else if (xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC) {
			return String.valueOf(xssfRow.getNumericCellValue());
		} else {
			return String.valueOf(xssfRow.getStringCellValue());
		}
		}else {
			return null;
		}
	}
}